package cn.oa.dao.impl;

import cn.oa.dao.BaseDao;
import cn.oa.dao.EmployeeDao;
import cn.oa.entity.Employee;
import cn.oa.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDaoImpl extends BaseDao implements EmployeeDao {
    public EmployeeDaoImpl(Connection conn) {
        super(conn);
    }
    @Override
    public Employee findEmployee(String employeeNo, String password) throws SQLException{
        ResultSet rs = null;
        String sql = "select * from employee e left join position p on e.positionId = p.id " +
                "left join department d on e.deptId = d.id " +
                "where employeeNo = ? and password = ? ";
        Employee employee = null;
        try{
            rs = this.executeQuery(sql, employeeNo,password);
            while(rs.next()){
                employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setEmployeeNo(rs.getString("employeeNo"));
                employee.setName(rs.getString("name"));
                employee.setPassword(rs.getString("password"));
                employee.setEmail(rs.getString("email"));
                employee.setEntryTime(rs.getDate("entryTime"));
                employee.setPosition(rs.getString("posName"));
                employee.setDepartment(rs.getString("deptName"));
                employee.setPhoto(rs.getString("photo"));
            }
        }catch(SQLException e){
            e.printStackTrace();
            throw e;
        }
        return employee;
    }

    @Override
    public List<Employee> getEmployeesByPage(String name, int pageNo, int pageSize)  throws SQLException {
        List<Employee> list = new ArrayList<Employee>();
        ResultSet rs = null;
        String sql = "select * from employee e left join position p on e.positionId = p.id left join department d on e.deptId = d.id ";
        if(name!=null && !name.equals("")){
            sql += "where name like '%" + name + "%' ";
        }
        sql += " order by e.id desc LIMIT ?, ?";
        try{
            rs = this.executeQuery(sql,(pageNo - 1) * pageSize,pageSize);
            while(rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getInt("id"));
                employee.setEmployeeNo(rs.getString("employeeNo"));
                employee.setName(rs.getString("name"));
                employee.setEmail(rs.getString("email"));
                employee.setEntryTime(rs.getDate("entryTime"));
                employee.setPosition(rs.getString("posName"));
                employee.setDepartment(rs.getString("deptName"));
                employee.setPhoto(rs.getString("photo"));
                list.add(employee);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return list;
    }

    @Override
    public int getEmployeesCount(String name) throws SQLException {
        int count = 0;
        ResultSet rs = null;
        String sql = "select count(*) from employee ";
        if(name!=null && !name.equals("")){
            sql += "where name like '%" + name + "%'";
        }
        try{
            rs = this.executeQuery(sql);
            while(rs.next()){
                count = rs.getInt("count(*)");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public int updateEmployee(Employee employee) throws SQLException {
        String sql = "update employee set employeeNo=?, name=?, email=?, entryTime=?, photo=?, deptId=?, positionId=? where id=?";
        int result=0;
        try{
            result = this.executeUpdate(sql, employee.getEmployeeNo(),employee.getName(),employee.getEmail(),employee.getEntryTime(),employee.getPhoto(),employee.getDeptId(),employee.getPositionId(), employee.getId());
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public int addNewEmployee(Employee employee) throws SQLException {
        String sql = "insert into employee (employeeNo, password, name, email, entryTime, photo, deptId,positionId) VALUES (?,?, ?, ?, ?, ?, ?, ?);";
        int result=0;
        String defaultPassword = "000000";
        try{
            result = this.executeUpdate(sql, employee.getEmployeeNo(),defaultPassword, employee.getName(),employee.getEmail(), employee.getEntryTime(),employee.getPhoto(),employee.getDeptId(),employee.getPositionId());
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public Employee getEmployeeById(int id) throws SQLException {
        String sql = "select * from employee e left join position p on e.positionId = p.id left join department d on e.deptId = d.id where e.id = ? ";
        Employee emp = new Employee();
        ResultSet rs = null;
        try{
            rs = this.executeQuery(sql,id);
            while(rs.next()){
                emp.setId(rs.getInt("id"));
                emp.setEmployeeNo(rs.getString("employeeNo"));
                emp.setName(rs.getString("name"));
                emp.setEmail(rs.getString("email"));
                emp.setEntryTime(rs.getDate("entryTime"));
                emp.setDeptId(rs.getInt("deptId"));
                emp.setPositionId(rs.getInt("positionId"));
                emp.setPosition(rs.getString("posName"));
                emp.setDepartment(rs.getString("deptName"));
                emp.setPhoto(rs.getString("photo"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return emp;
    }

    @Override
    public int getEmployeeByNo(String employeeNo) throws SQLException {
        int count = 0;
        ResultSet rs = null;
        String sql = "select count(*) from employee where employeeNo=?";
        try{
            rs = this.executeQuery(sql,employeeNo);
            while(rs.next()){
                count = rs.getInt("count(*)");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public int deleteEmployeeById(int id) throws SQLException {
        String sql = "delete from employee where id = ?";
        int result=0;
        try{
            result = this.executeUpdate(sql, id);
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public int updatePasswordById(int id, String pwd) throws SQLException {
        int result = 0;
        String sql = "update employee set password = ? where id = ?";
        try{
            result = this.executeUpdate(sql,pwd,id);
        }catch(SQLException e){
            e.printStackTrace();
            throw e;
        }
        return result;
    }
}
